<?php /* УТФ-8:fix */

class SPImodel__content__front extends SPImodel__content
{
    public function select( $type, $orderby, $limit=null, $catIdList = null, $offset = 0, $filterIds = null )
    {
		$FILTER_SUBQUERY = $this->getFiltersSubquery( $filterIds );
		
		switch( $orderby )
        {
            default:
            case 'name':        $orderby = 'name ASC, id ASC'; break;
            case 'order_index': $orderby = 'order_index ASC, id DESC'; break;
            case 'pubdate-':    $orderby = 'pubdate DESC, id DESC'; break;
            case 't_created-':  $orderby = 't_created DESC, id DESC'; break;
            case 'random':  	$orderby = 'rand()'; break;
        }

        $limit  = (int) $limit;
        $offset = (int) $offset;
		
		if( is_array( $catIdList ) === false )
			$catIdList = array();

        $query =    " SELECT cnt.*, brand.name as brand "
               .      " FROM ".SPIcfg::$store['table:content'] . " cnt "
               .      " LEFT JOIN ".SPIcfg::$store['table:brands'] . " brand ON( brand.id = cnt.brand ) "
               .     " WHERE workstatus=1 "
               .       " AND type = '".addslashes($type)."' "
               .       ( count($catIdList) == 0 ? "" : " AND pid_catalog IN ( ".join( ',', $catIdList )." ) " )
			   .      ( $FILTER_SUBQUERY ? " AND cnt.id IN ( ".$FILTER_SUBQUERY." ) " : "" )
               .  	 " ORDER BY {$orderby} "
               .     ( $limit ? " LIMIT ".(int)$limit : '' )
			   .     " OFFSET {$offset} "
               ;

        return SPIstore::I()->query( $query );
    }
	
	public function selectCount( $type, $catIdList = null, $filterIds = null )
    {
        if( is_array( $catIdList ) === false )
			$catIdList = array();
		
		$FILTER_SUBQUERY = $this->getFiltersSubquery( $filterIds );
		
		$query =    " SELECT count(id) as count"
               .      " FROM ".SPIcfg::$store['table:content'] . " cnt "
               .     " WHERE workstatus=1 "
               .       " AND type = '".addslashes($type)."' "
               .	   ( count($catIdList) == 0 ? "" : " AND pid_catalog IN ( ".join( ',', $catIdList )." ) " )
			   .      ( $FILTER_SUBQUERY ? " AND cnt.id IN ( ".$FILTER_SUBQUERY." ) " : "" )
               ;

        $res = SPIstore::I()->query( $query )->fetch_and_close();
		return (int)$res['count'];
    }
	
	private function getFiltersSubquery( $filterIds )
	{
		if( is_array( $filterIds ) && count($filterIds) > 0 )
		{
			$filters = array();
			$FILTER_SUBQUERY = null;
			$QUERY_FILTER_WHERE = null;
			
			foreach( $filterIds as $filterId )
			{
				$filterItem = SPIm::I('filters')->front->get_by_id( $filterId );
				if( $filterItem === false )
					continue;
				
				$filters[$filterItem['pid']][] = $filterItem;
			}
			
			
			$FILTER_JOIN_GROUP_ADD = array();
			
			foreach( $filters as $groupId => $filterGroup )
			{
				$FILTER_WHERE_ADD_OR = array();
				
				foreach( $filterGroup as $filter )
				{
					$FILTER_WHERE_ADD_AND = array();
					
					$FILTER_WHERE_ADD_AND[] = 'filter.id = '.(int)$filter['id'];
					
					switch( $filter['method_equal'] )
					{
						case '=' :
							/*
								check: need it to convert to decimal filter.`value`??
								DECIMAL(20,4) => думаю хватит знаков.. если нехватит то некоторые вещи могу неверно сравниватся..
							*/
							$FILTER_WHERE_ADD_AND[] = 'CAST(param.`value` AS DECIMAL(20,4)) = filter.`value`';
							break;
						case '%' :
							$FILTER_WHERE_ADD_AND[] = 'LOWER( param.`value` ) = LOWER( filter.`value` )';
							break;
						case '>' :
							$FILTER_WHERE_ADD_AND[] = 'CAST(param.`value` AS DECIMAL(20,4)) > filter.`value`';
							if( $filter['value_add'] )
							{
								$FILTER_WHERE_ADD_AND[] = 'CAST(param.`value` AS DECIMAL(20,4)) < filter.`value_add`';
							}	
							break;
						case '<' :
							$FILTER_WHERE_ADD_AND[] = 'CAST(param.`value` AS DECIMAL(20,4)) < filter.`value`';
							if( $filter['value_add'] )
							{
								$FILTER_WHERE_ADD_AND[] = 'CAST(param.`value` AS DECIMAL(20,4)) > filter.`value_add`';
							}	
							break;
						default:
							$FILTER_WHERE_ADD_AND[] = 'FALSE';
					}
					
					$FILTER_WHERE_ADD_OR[] = ' ( '. join( ' AND ', $FILTER_WHERE_ADD_AND ) . ' ) ';
				}
				
				$QUERY_FILTER_WHERE = ' ( '. join( ' OR ', $FILTER_WHERE_ADD_OR ) . ' ) ';
				
				if( $FILTER_SUBQUERY === null )
				{
					$FILTER_SUBQUERY = "
						SELECT *
						FROM
						(
							SELECT param.pid_content 
								FROM ".SPIcfg::$store['table:content_params']." as param
								JOIN ".SPIcfg::$store['table:filters']." as filter ON ( filter.pid_params = param.pid_params )
							WHERE param.value IS NOT NULL
								AND filter.workstatus = 1
								AND	".$QUERY_FILTER_WHERE."
						) as subquery_group_{$groupId}";
				}
				else
				{
					$FILTER_JOIN_GROUP_ADD[] =  "
						JOIN (
							SELECT param.pid_content 
								FROM ".SPIcfg::$store['table:content_params']." as param
								JOIN ".SPIcfg::$store['table:filters']." as filter ON ( filter.pid_params = param.pid_params )
							WHERE param.value IS NOT NULL
								AND filter.workstatus = 1
								AND	".$QUERY_FILTER_WHERE."
						) as subquery_group_{$groupId}
						USING( pid_content  )
					";
				}
			}
			$FILTER_SUBQUERY .= join( ' ', $FILTER_JOIN_GROUP_ADD );
			
			return $FILTER_SUBQUERY;
		}
		
		return false;
	}

    public function get_by_id( $item_id )
    {
		$query =    " SELECT cnt.*, brand.name as brand "
               .      " FROM ".SPIcfg::$store['table:content'] . " cnt "
               .      " LEFT JOIN ".SPIcfg::$store['table:brands'] . " brand ON( brand.id = cnt.brand ) "
               .     " WHERE cnt.id=" . (int) $item_id
               .       " AND cnt.workstatus=1 "
               ;

        return SPIstore::I()->query( $query )->fetch_and_close();
    }
}
